﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;

namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:ProjectsRelation
	/// </summary>
	public partial class ProjectRelation
	{
		public ProjectRelation()
		{}
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "ProjectsRelation");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int? id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ProjectsRelation");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataLib.Model.ProjectsRelation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ProjectsRelation(");
            strSql.Append("ServerAreaID,ServerPointID,ProjectID,ProjectName,ProjectNum,AreaName,ServerPointName,AddressID,AddresssName,SalesmanID,SalesmanTruename,SlaesmanUsername,CreateTime,CreateMan)");
            strSql.Append(" values (");
            strSql.Append("@ServerAreaID,@ServerPointID,@ProjectID,@ProjectName,@ProjectNum,@AreaName,@ServerPointName,@AddressID,@AddresssName,@SalesmanID,@SalesmanTruename,@SlaesmanUsername,@CreateTime,@CreateMan)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ServerAreaID", SqlDbType.Int,4),
					new SqlParameter("@ServerPointID", SqlDbType.Int,4),
					new SqlParameter("@ProjectID", SqlDbType.Int,4),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@AreaName", SqlDbType.NVarChar,50),
					new SqlParameter("@ServerPointName", SqlDbType.NVarChar,50),
					new SqlParameter("@AddressID", SqlDbType.Int,4),
					new SqlParameter("@AddresssName", SqlDbType.NVarChar,50),
					new SqlParameter("@SalesmanID", SqlDbType.Int,4),
					new SqlParameter("@SalesmanTruename", SqlDbType.NVarChar,50),
					new SqlParameter("@SlaesmanUsername", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@CreateMan", SqlDbType.VarChar,20)};
            parameters[0].Value = model.ServerAreaID;
            parameters[1].Value = model.ServerPointID;
            parameters[2].Value = model.ProjectID;
            parameters[3].Value = model.ProjectName;
            parameters[4].Value = model.ProjectNum;
            parameters[5].Value = model.AreaName;
            parameters[6].Value = model.ServerPointName;
            parameters[7].Value = model.AddressID;
            parameters[8].Value = model.AddresssName;
            parameters[9].Value = model.SalesmanID;
            parameters[10].Value = model.SalesmanTruename;
            parameters[11].Value = model.SlaesmanUsername;
            parameters[12].Value = model.CreateTime;
            parameters[13].Value = model.CreateMan;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DataLib.Model.ProjectsRelation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ProjectsRelation set ");
            strSql.Append("ServerAreaID=@ServerAreaID,");
            strSql.Append("ServerPointID=@ServerPointID,");
            strSql.Append("ProjectID=@ProjectID,");
            strSql.Append("ProjectName=@ProjectName,");
            strSql.Append("ProjectNum=@ProjectNum,");
            strSql.Append("AreaName=@AreaName,");
            strSql.Append("ServerPointName=@ServerPointName,");
            strSql.Append("AddressID=@AddressID,");
            strSql.Append("AddresssName=@AddresssName,");
            strSql.Append("SalesmanID=@SalesmanID,");
            strSql.Append("SalesmanTruename=@SalesmanTruename,");
            strSql.Append("SlaesmanUsername=@SlaesmanUsername,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("CreateMan=@CreateMan");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ServerAreaID", SqlDbType.Int,4),
					new SqlParameter("@ServerPointID", SqlDbType.Int,4),
					new SqlParameter("@ProjectID", SqlDbType.Int,4),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@AreaName", SqlDbType.NVarChar,50),
					new SqlParameter("@ServerPointName", SqlDbType.NVarChar,50),
					new SqlParameter("@AddressID", SqlDbType.Int,4),
					new SqlParameter("@AddresssName", SqlDbType.NVarChar,50),
					new SqlParameter("@SalesmanID", SqlDbType.Int,4),
					new SqlParameter("@SalesmanTruename", SqlDbType.NVarChar,50),
					new SqlParameter("@SlaesmanUsername", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@CreateMan", SqlDbType.VarChar,20),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = model.ServerAreaID;
            parameters[1].Value = model.ServerPointID;
            parameters[2].Value = model.ProjectID;
            parameters[3].Value = model.ProjectName;
            parameters[4].Value = model.ProjectNum;
            parameters[5].Value = model.AreaName;
            parameters[6].Value = model.ServerPointName;
            parameters[7].Value = model.AddressID;
            parameters[8].Value = model.AddresssName;
            parameters[9].Value = model.SalesmanID;
            parameters[10].Value = model.SalesmanTruename;
            parameters[11].Value = model.SlaesmanUsername;
            parameters[12].Value = model.CreateTime;
            parameters[13].Value = model.CreateMan;
            parameters[14].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ProjectsRelation ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ProjectsRelation ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.ProjectsRelation GetModel(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,ServerAreaID,ServerPointID,ProjectID,ProjectName,ProjectNum,AreaName,ServerPointName,AddressID,AddresssName,SalesmanID,SalesmanTruename,SlaesmanUsername,CreateTime,CreateMan from ProjectsRelation ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            DataLib.Model.ProjectsRelation model = new DataLib.Model.ProjectsRelation();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.ProjectsRelation DataRowToModel(DataRow row)
        {
            DataLib.Model.ProjectsRelation model = new DataLib.Model.ProjectsRelation();
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.id = int.Parse(row["ID"].ToString());
                }
                if (row["ServerAreaID"] != null && row["ServerAreaID"].ToString() != "")
                {
                    model.ServerAreaID = int.Parse(row["ServerAreaID"].ToString());
                }
                if (row["ServerPointID"] != null && row["ServerPointID"].ToString() != "")
                {
                    model.ServerPointID = int.Parse(row["ServerPointID"].ToString());
                }
                if (row["ProjectID"] != null && row["ProjectID"].ToString() != "")
                {
                    model.ProjectID = int.Parse(row["ProjectID"].ToString());
                }
                if (row["ProjectName"] != null)
                {
                    model.ProjectName = row["ProjectName"].ToString();
                }
                if (row["ProjectNum"] != null)
                {
                    model.ProjectNum = row["ProjectNum"].ToString();
                }
                if (row["AreaName"] != null)
                {
                    model.AreaName = row["AreaName"].ToString();
                }
                if (row["ServerPointName"] != null)
                {
                    model.ServerPointName = row["ServerPointName"].ToString();
                }
                if (row["AddressID"] != null && row["AddressID"].ToString() != "")
                {
                    model.AddressID = int.Parse(row["AddressID"].ToString());
                }
                if (row["AddresssName"] != null)
                {
                    model.AddresssName = row["AddresssName"].ToString();
                }
                if (row["SalesmanID"] != null && row["SalesmanID"].ToString() != "")
                {
                    model.SalesmanID = int.Parse(row["SalesmanID"].ToString());
                }
                if (row["SalesmanTruename"] != null)
                {
                    model.SalesmanTruename = row["SalesmanTruename"].ToString();
                }
                if (row["SlaesmanUsername"] != null)
                {
                    model.SlaesmanUsername = row["SlaesmanUsername"].ToString();
                }
                if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
                }
                if (row["CreateMan"] != null)
                {
                    model.CreateMan = row["CreateMan"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,ServerAreaID,ServerPointID,ProjectID,ProjectName,ProjectNum,AreaName,ServerPointName,AddressID,AddresssName,SalesmanID,SalesmanTruename,SlaesmanUsername,CreateTime,CreateMan ");
            strSql.Append(" FROM ProjectsRelation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,ServerAreaID,ServerPointID,ProjectID,ProjectName,ProjectNum,AreaName,ServerPointName,AddressID,AddresssName,SalesmanID,SalesmanTruename,SlaesmanUsername,CreateTime,CreateMan ");
            strSql.Append(" FROM ProjectsRelation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM ProjectsRelation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ID desc");
            }
            strSql.Append(")AS Row, T.*  from ProjectsRelation T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "ProjectsRelation";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
		#region  ExtensionMethod

        /// <summary>
        /// 根据用户名取得名下所有可开CJO
        /// </summary>
        /// <param name="uerName"></param>
        /// <param name="typeflag">
        /// 参数 @getTye 0 取得@username权限 如果admin 1(开cjo)_1(授权)_1(查看) 
        ///				 1 取得@username的开CJO列表
        ///				 2 取得@username的CJO授权列表
        ///				 3 取得@username的查看列表</param>
        /// <returns></returns>
        public DataTable getAddressByUserName(string uerName, int typeflag)
        {
            DataTable dt = new DataTable();

            SqlParameter[] sqlparameter ={
                new SqlParameter("@username",SqlDbType.VarChar,20),
                new SqlParameter("@getType",SqlDbType.Int)
              };
            sqlparameter[0].Value = uerName;
            sqlparameter[1].Value = typeflag;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetProjectDetail", sqlparameter);
            dt.Load(dr);
            dr.Close();


            return dt;

        }
        /// <summary>
        /// 级联删除
        /// </summary>
        /// <param name="AddressID">地址编号</param>
        /// <param name="ServerPointID">服务点编号</param>
        /// <param name="ServerAreaID">服务区域编号</param>
        /// <param name="ProjectID">项目编号</param>
        /// <param name="DelType">删除类型(1只删除地址,2删除地址和服务点,3删除地址、服务点和区域,4删除地址、服务点、区域和项目)</param>
        public void RelationDelete(int AddressID, int ServerPointID, int ServerAreaID, int ProjectID, int DelType)
        {
            SqlParameter[] sqlparameters = {
                         new SqlParameter("@AddressID",SqlDbType.Int),
                         new SqlParameter("@ServerPointID",SqlDbType.Int),
                         new SqlParameter("@ServerAreaID",SqlDbType.Int),
                         new SqlParameter("@ProjectID",SqlDbType.Int),
                         new SqlParameter("@DelType",SqlDbType.Int)
            };
            sqlparameters[0].Value = AddressID;
            sqlparameters[1].Value = ServerPointID;
            sqlparameters[2].Value = ServerAreaID;
            sqlparameters[3].Value = ProjectID;
            sqlparameters[4].Value = DelType;
            DbHelperSQL.RunProcedure("EWMS_Relation_Delete", sqlparameters);
        }

        /// <summary>
        /// 根据客户ID取得客户名下项目列表
        /// </summary>
        /// <param name="uerName"></param>
        /// <param name="typeflag">
        /// 参数 @getTye 0 取得@username权限 如果admin 1(开cjo)_1(授权)_1(查看) 
        ///				 1 取得@username的开CJO列表
        ///				 2 取得@username的CJO授权列表
        ///				 3 取得@username的查看列表</param>
        ///	<param name="cumid">客户ID</param>
        /// <returns></returns>
        public DataTable getAddressByUserName(string uerName, int typeflag,int cumid)
        {
            DataTable dt = new DataTable();
            if (typeflag != 6)
            {
                SqlParameter[] sqlparameter ={
                new SqlParameter("@username",SqlDbType.VarChar,20),
                new SqlParameter("@getType",SqlDbType.Int),
                new SqlParameter("@cumid",SqlDbType.Int)
              };
                sqlparameter[0].Value = uerName;
                sqlparameter[1].Value = typeflag;
                sqlparameter[2].Value = cumid;
                SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetProjectDetail", sqlparameter);
                dt.Load(dr);
                dr.Close();
            }
            else
            {
                StringBuilder strsql = new StringBuilder("");
                strsql.Append("select * from ");
                strsql.Append("(select row_number() over(partition by l.id order by l.loadnum) as rownum,l.id l_id,l.labname ");
                strsql.Append(",p.ProjectID proid,p.ProjectName proname,p.ProjectNum pronum,l.cumcode,l.loadnum ");
                strsql.Append(",l.cumname,p.belongAddress,ca.address addressinfo,c.cjocolldate,REPLACE(c.sealman_show,'/','-') cjotruename ");
                strsql.Append(",l.totalWeight,l.finishWeight,l.Memo,c.customerid cumid,c.status c_status ");
                strsql.Append(",case c.status when 1 then '待派车' ");
                strsql.Append("when 2 then '待称重' ");
                strsql.Append("when 3 then '待授权' ");
                strsql.Append("when 4 then '待拆解' ");
                strsql.Append("when 5 then '拆解中' ");
                strsql.Append("when -4 then '拆解申请审批中' ");
                strsql.Append("when -9 then '作废' ");
                strsql.Append("END cstatus ");
                strsql.Append("	from Project p,Cjo c,LoadNum l,ProjectsRelation pr,CumtomerAddress ca ");
                strsql.Append("where l.projectid=p.ProjectID and c.loadnumid=l.id and c.addid=pr.ID and ca.id=pr.AddressID) t ");
                strsql.Append("where rownum=1 ");
                dt = DbHelperSQL.Query(strsql.ToString()).Tables[0];
            }
            return dt;

        }


        /// <summary>
        /// 根据客户ID取得客户名下项目列表
        /// </summary>
        /// <param name="uerName"></param>
        /// <param name="typeflag">
        /// 参数 @getTye 0 取得@username权限 如果admin 1(开cjo)_1(授权)_1(查看) 
        ///				 1 取得@username的开CJO列表
        ///				 2 取得@username的CJO授权列表
        ///				 3 取得@username的查看列表</param>
        ///	<param name="cumid">客户ID</param>
        /// <returns></returns>
        public DataTable getAddressByUserName(string uerName, int typeflag, int proid, int cumid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@username",SqlDbType.VarChar,20),
                new SqlParameter("@getType",SqlDbType.Int),
                new SqlParameter("@proid",SqlDbType.Int)
              };
            sqlparameter[0].Value = uerName;
            sqlparameter[1].Value = typeflag;
            sqlparameter[2].Value = proid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetProjectDetail", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;

        }

        /// <summary>
        /// 根据用户名取得名下所有项目或者区域
        /// </summary>
        /// <param name="uerName"></param>
        /// <param name="typeflag">
        /// 参数 @getTye 
        ///				 1 取得@username的项目列表
        ///				 2 取得项目下面的的服务点
        ///				 3 取得@username的查看列表
        ///	</param>
        /// <returns></returns>
        public DataTable getProjectByUserName(string uerName, int typeflag)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@username",SqlDbType.VarChar,20),
                new SqlParameter("@getType",SqlDbType.Int)
              };
            sqlparameter[0].Value = uerName;
            sqlparameter[1].Value = typeflag;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetPointList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;

        }

        /// <summary>
        /// 2015年5月28日 21:30:49
        /// FIFA
        /// LoadNumByCustomerList.aspx 分页
        /// </summary>
        /// <param name="uerName"></param>
        /// <param name="currpage"></param>
        /// <param name="length"></param>
        /// <param name="totalRecord"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public DataTable GetProjectDetailsByStore(string uerName, int currpage, int length, out int totalRecord, string search,string city, string order_column, string order_dir,string uname)
        {
            totalRecord = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@currpage",SqlDbType.Int),
                new SqlParameter("@Length",SqlDbType.Int),
                new SqlParameter("@totalcount",SqlDbType.Int),
                new SqlParameter("@search",SqlDbType.VarChar,100),
                new SqlParameter("@city",SqlDbType.VarChar,50),
                new SqlParameter("@order_column",SqlDbType.VarChar,20),
                new SqlParameter("@order_dir",SqlDbType.VarChar,20),
                new SqlParameter("@uname",SqlDbType.VarChar,20)

             };
            sqlparameter[0].Value = currpage;
            sqlparameter[1].Value = length;
            sqlparameter[2].Value = totalRecord;
            sqlparameter[2].Direction = ParameterDirection.Output;
            sqlparameter[3].Value = search;
            sqlparameter[4].Value = city;
            sqlparameter[5].Value = order_column;
            sqlparameter[6].Value = order_dir;
            sqlparameter[7].Value = uname;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetProjectDetailByStore", sqlparameter);
            dt.Load(dr);
            totalRecord = Convert.ToInt32(sqlparameter[2].Value);
            dr.Close();
            return dt;

        }



		#endregion  ExtensionMethod
	}
}

